﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using System.Data;

namespace DVSProject.Lib
{
    public class LocationSetRepo
    {
        public const string m_msg_head = "LocationSet";

        public IEnumerable<LocationSetModels> GetLocationSet()
        {
            List<LocationSetModels> LocationSets = new List<LocationSetModels>();
            string SQLCommand = @"select TBLocationSet.*,TBMerchant.NameTh AS Merchant_NameEn from TBLocationSet LEFT JOIN TBMerchant
                                  ON TBLocationSet.MerchantId = TBMerchant.MerchantId WHERE TBLocationSet.Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    int i = 0;

                    while (reader.Read())
                    {
                        i = i + 1;
                        LocationSetModels LocationSet = new LocationSetModels();
                        LocationSet.RowCount = i;
                        LocationSet.LocSetId = (int)reader["LocSetId"];
                        LocationSet.LocationSetCode = (string)reader["LocationSetCode"];
                        LocationSet.MerchantId = (int)reader["MerchantId"];
                        LocationSet.Merchant_NameEn = (string)reader["Merchant_NameEn"];
                        LocationSet.LocSetNameTh = (string)reader["LocSetNameTh"];
                        LocationSet.LocSetNameEng = (string)reader["LocSetNameEng"];
                        LocationSet.Status = (string)reader["Status"];
                        LocationSet.CreateBy = (string)reader["CreateBy"];
                        LocationSet.CreateOn = (DateTime)reader["CreateOn"];
                        LocationSet.UpdateBy = (string)reader["UpdateBy"];
                        LocationSet.UpdateOn = (DateTime)reader["UpdateOn"];

                        LocationSets.Add(LocationSet);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return LocationSets.ToList();
        }

        public LocationSetModels GetLocationSetById(int id)
        {
            LocationSetModels LocationSet = new LocationSetModels();
            string SQLCommand = @"select * from TBLocationSet where LocSetId=@LocSetId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@LocSetId", id);
                    int i = 0;
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        i = i + 1;
                        LocationSet.RowCount = i ;
                        LocationSet.LocSetId = (int)reader["LocSetId"];
                        LocationSet.LocationSetCode = (string)reader["LocationSetCode"];
                        LocationSet.MerchantId = (int)reader["MerchantId"];
                        LocationSet.LocSetNameTh = (string)reader["LocSetNameTh"];
                        LocationSet.LocSetNameEng = (string)reader["LocSetNameEng"];
                        LocationSet.Status = (string)reader["Status"];
                        LocationSet.CreateBy = (string)reader["CreateBy"];
                        LocationSet.CreateOn = (DateTime)reader["CreateOn"];
                        LocationSet.UpdateBy = (string)reader["UpdateBy"];
                        LocationSet.UpdateOn = (DateTime)reader["UpdateOn"];
                        
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return LocationSet;
        }
        public bool CheckDub(string LocationSetCode)
        {
            bool result ;
           
            DataSet SummaryMeters = new DataSet();
            string SQLCommand = @" Select LocationSetCode, MerchantId  From TBLocationSet  Where LocationSetCode=@LocationSetCode";

            //DECLARE @CODE_NO1	varchar(4)
            //                    DECLARE @LASTNO		int
            //                    SET @LASTNO =(select isnull(max(right(isnull(LocationSetCode ,0),3)),0)+1   from TBLocationSet WHERE LocationSetCode <> 'NS02' )
            //                    SET @CODE_NO1='M' + STUFF('000', len('000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)
            using (var db = new DBHelper())
            {
                try
                {

                    db.AddParameter("@LocationSetCode", LocationSetCode);
                    SummaryMeters = db.ExecuteDataSet(SQLCommand);
                    if (SummaryMeters.Tables[0].Rows.Count > 0)
                    {
                        result = true;
                    }
                    else { result = false; }
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return true;
                }
            }
            return result;
        }
        public bool Create(LocationSetModels LocationSet)
        {
            bool result = true;
            if (!CheckDub(LocationSet.LocationSetCode))
            {

            string SQLCommand = @" INSERT into TBLocationSet
                                    values (@LocationSetCode ,@MerchantId,@LocSetNameTh ,@LocSetNameEng ,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            //DECLARE @CODE_NO1	varchar(4)
            //                    DECLARE @LASTNO		int
            //                    SET @LASTNO =(select isnull(max(right(isnull(LocationSetCode ,0),3)),0)+1   from TBLocationSet WHERE LocationSetCode <> 'NS02' )
            //                    SET @CODE_NO1='M' + STUFF('000', len('000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)
            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    //db.AddParameter("@LocSetId", "CallGenId");
                    db.AddParameter("@LocationSetCode", LocationSet.LocationSetCode);
                    db.AddParameter("@MerchantId", LocationSet.MerchantId);
                    db.AddParameter("@LocSetNameTh", LocationSet.LocSetNameTh);
                    db.AddParameter("@LocSetNameEng", LocationSet.LocSetNameEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", LocationSet.CreateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }

            }
            return result;
        }

        public bool Edit(LocationSetModels LocationSet)
        {
            bool result = true;
            string SQLCommand = @"Update TBLocationSet 
                                    Set LocationSetCode=@LocationSetCode,
                                        MerchantId = @MerchantId,
                                        LocSetNameTh = @LocSetNameTh,
                                        LocSetNameEng = @LocSetNameEng,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where LocSetId=@LocSetId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@LocSetId", LocationSet.LocSetId);
                    db.AddParameter("@LocationSetCode", LocationSet.LocationSetCode);
                    db.AddParameter("@MerchantId", LocationSet.MerchantId);
                    db.AddParameter("@LocSetNameTh", LocationSet.LocSetNameTh);
                    db.AddParameter("@LocSetNameEng", LocationSet.LocSetNameEng);
                    db.AddParameter("@UserID", LocationSet.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(LocationSetModels LocationSet)
        {
            bool result = true;
            string SQLCommand = @"Update TBLocationSet
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where LocSetId=@LocSetId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@LocSetId", LocationSet.LocSetId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", LocationSet.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}